package cn.lanqiao.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import cn.lanqiao.dao.StudentDao;
import cn.lanqiao.util.JdbcUtil;

public class StudentDaoImpl implements StudentDao {

	@Override
	public Object[][] getCourse(String sid) {
		//创建一个连接对象
		Connection conn=JdbcUtil.getConnection();
		//创建statement对象
		PreparedStatement ps=null;
		//将获取到的值赋给一个student对象
		ResultSet rs=null;
		int count = this.courseCount(sid);
		//创建一个数组，将得到的数据放到一个数组中
		Object[][] datas =new Object[count][] ;
		String sql="select cname from EDU_COURSE c,EDU_SCORE s where c.CID=s.SCOURSE and sid=?";
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, sid);
			rs=ps.executeQuery();
			for(int i=0;rs.next();i++) {
				datas[i] =new Object[] {rs.getString("cname")};
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(rs, ps, conn);
		}
		return datas;
	}
	
	public int courseCount(String sid) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps =null;
		ResultSet rs =null;
		//获取课程的列数
		String sql="select count(cname) from EDU_COURSE c,EDU_SCORE s where c.CID=s.SCOURSE and sid=?";
		int num = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, sid);
			rs = ps.executeQuery();
			if(rs.next()) {
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
		return num;
	}

	@Override
	public Object[][] getSidSgrade(String sid) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps =null;
		ResultSet rs =null;
		int count = this.countGrade(sid);
		Object[][] datas =new Object[count][] ;
		String sql="select cname,sgrade from Edu_score s,EDU_COURSE c where s.SCOURSE=c.CID and sid = ?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1,sid);
			rs = ps.executeQuery();
			for(int i=0;rs.next();i++) {
				datas[i] =new Object[] {rs.getString(1),rs.getString(2)};
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
		return datas;
	}
	
	@Override
	public int countGrade(String sid) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps =null;
		ResultSet rs =null;
		String sql="select count(cname) from Edu_score s,EDU_COURSE c where s.SCOURSE=c.CID and sid = ?";
		int num = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1,sid);
			rs = ps.executeQuery();
			
			if(rs.next()) {
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs, ps, conn);
		}
		return num;
	}

	@Override
	public void changeupassword(String sid, String newPassword) {
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps =null;
		String updateSql = "update Edu_users set password = ? where Account = ?";
		try {
			ps = conn.prepareStatement(updateSql);
			ps.setString(1, newPassword);
			ps.setString(2, sid);
			int affter = ps.executeUpdate();
			if(affter>0) {
				System.out.println("密码修改成功");
			} else {
				System.out.println("密码修改失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(null, ps, conn);
		}
	}
}
